﻿using BridgeWebSystemLib.Core.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace BridgeWebSystemLib.Core.DataAccess
{
    public static class SQLServerHelper
    {
        #region ExecuteNonQuery
        public static int ExecuteNonQuery(this SqlConnection connection, string commandText, SqlParameter[] commandParameters, SqlTransaction transaction = null)
        {
            return ExecuteNonQuery(connection, commandText, CommandType.Text, commandParameters, transaction);
        }

        public static int ExecuteNonQuery(this SqlConnection connection, string commandText, CommandType commandType, SqlParameter[] commandParameters, SqlTransaction transaction = null)
        {
            SqlCommand command = null;
            if (transaction == null)
            {
                command = connection.CreateCommand();
                PrepareCommand(command, connection, null, commandText, commandType, commandParameters);
            }
            else
            {
                command = transaction.Connection.CreateCommand();
                PrepareCommand(command, transaction.Connection, transaction, commandText, commandType, commandParameters);
            }
            SqlServerSqlLog(commandText, commandParameters);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result;
        }

        public static async Task<int> ExecuteNonQueryAsync(this SqlConnection connection, string commandText, SqlParameter[] commandParameters, SqlTransaction transaction = null)
        {
            return await ExecuteNonQueryAsync(connection, commandText, CommandType.Text, commandParameters, transaction);
        }

        public static async Task<int> ExecuteNonQueryAsync(this SqlConnection connection, string commandText, CommandType commandType, SqlParameter[] commandParameters, SqlTransaction transaction = null)
        {
            SqlCommand command = null;
            if (transaction == null)
            {
                command = connection.CreateCommand();
                PrepareCommand(command, connection, null, commandText, commandType, commandParameters);
            }
            else
            {
                command = transaction.Connection.CreateCommand();
                PrepareCommand(command, transaction.Connection, transaction, commandText, commandType, commandParameters);
            }

            SqlServerSqlLog(commandText, commandParameters);
            int result = await command.ExecuteNonQueryAsync();
            command.Dispose();
            return result;
        }
        #endregion

        #region ExecuteDataTable
        public static DataTable ExecuteDataTable(this SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteDataTable(connection, commandText, CommandType.Text, commandParameters);
        }

        public static DataTable ExecuteDataTable(this SqlConnection connection, string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            DataTable dataTable = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, null, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                using (var reader = command.ExecuteReader())
                {
                    dataTable = new DataTable();
                    dataTable.Load(reader);
                }
            }
            return dataTable;
        }

        public static async Task<DataTable> ExecuteDataTableAsync(this SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
        {
            return await ExecuteDataTableAsync(connection, commandText, CommandType.Text, commandParameters);
        }

        public static async Task<DataTable> ExecuteDataTableAsync(this SqlConnection connection, string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            DataTable dataTable = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, null, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                using (var reader = await command.ExecuteReaderAsync())
                {
                    dataTable = new DataTable();
                    await Task.Run(() =>
                    {
                        dataTable.Load(reader);
                    });
                }
            }
            return dataTable;
        }
        #endregion

        #region ExecuteScalar
        public static object ExecuteScalar(this SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
        {
            return ExecuteScalar(connection, commandText, null, CommandType.Text, commandParameters);
        }

        public static object ExecuteScalar(this SqlConnection connection, string commandText,SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return ExecuteScalar(connection, commandText, transaction, CommandType.Text, commandParameters);
        }

        public static object ExecuteScalar(this SqlConnection connection, string commandText, SqlTransaction transaction, CommandType commandType, params SqlParameter[] commandParameters)
        {
            object result = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, transaction, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                result = command.ExecuteScalar();
            }
            return result;
        }

        public static async Task<object> ExecuteScalarAsync(this SqlConnection connection, string commandText, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return await ExecuteScalarAsync(connection, commandText, transaction, CommandType.Text, commandParameters);
        }

        public static async Task<object> ExecuteScalarAsync(this SqlConnection connection, string commandText, SqlTransaction transaction, CommandType commandType, params SqlParameter[] commandParameters)
        {
            object result = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, transaction, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                result = await command.ExecuteScalarAsync();
            }
            return result;
        }
        #endregion

        #region ExecuteReader
        public static IDataReader ExecuteReader(this SqlConnection connection, string commandText,SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return ExecuteReader(connection, commandText, CommandType.Text, transaction, commandParameters);
        }

        public static IDataReader ExecuteReader(this SqlConnection connection, string commandText, CommandType commandType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            IDataReader reader = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, transaction, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                reader = command.ExecuteReader();
            }
            return reader;
        }

        public static async Task<IDataReader> ExecuteReaderAsync(this SqlConnection connection, string commandText, params SqlParameter[] commandParameters)
        {
            return await ExecuteReaderAsync(connection, commandText, CommandType.Text, commandParameters);
        }

        public static async Task<IDataReader> ExecuteReaderAsync(this SqlConnection connection, string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            IDataReader reader = null;
            using (SqlCommand command = connection.CreateCommand())
            {
                PrepareCommand(command, connection, null, commandText, commandType, commandParameters);
                SqlServerSqlLog(commandText, commandParameters);
                reader = await command.ExecuteReaderAsync();
            }
            return reader;
        }
        #endregion

        #region PrivateMethod
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, string commandText, CommandType commandType, params SqlParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = transaction;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            foreach (SqlParameter parameter in commandParameters)
            {
                if (parameter.Direction == ParameterDirection.Input)
                {
                    if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.SmallInt || parameter.SqlDbType == SqlDbType.BigInt)
                    {
                        if (parameter.Value == null || (int)parameter.Value == DBUtil.INVALID_INT)
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                    else if (parameter.SqlDbType == SqlDbType.VarChar || parameter.SqlDbType == SqlDbType.NVarChar)
                    {
                        if (parameter.Value == null || parameter.Value.ToString() == "")
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                    else if (parameter.SqlDbType == SqlDbType.Decimal)
                    {
                        if (parameter.Value == null || (decimal)parameter.Value == DBUtil.INVALID_DECIMAL)
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                    else if (parameter.SqlDbType == SqlDbType.Date || parameter.SqlDbType == SqlDbType.DateTime)
                    {
                        if (parameter.Value == null || (DateTime)parameter.Value == DBUtil.INVALID_DATE)
                        {
                            parameter.Value = DBNull.Value;
                        }
                    }
                }
                command.Parameters.Add(parameter);
            }
        }

        private static void SqlServerSqlLog(string sSql, SqlParameter[] commandParameters)
        {
            string sSqllog = sSql;
            if (commandParameters != null)
            {
                foreach (var param in commandParameters)
                {
                    string value = param.Value.ToString();
                    if (param.Value != DBNull.Value)
                    {
                        if (param.SqlDbType == SqlDbType.VarChar)
                        {
                            value = "'" + param.Value + "'";
                        }
                        else if (param.SqlDbType == SqlDbType.Date)
                        {
                            value = ((DateTime)param.Value).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        else if (param.SqlDbType == SqlDbType.Int)
                        {
                            value = ((int)param.Value).ToString();
                        }
                        else if (param.SqlDbType == SqlDbType.Decimal)
                        {
                            value = ((decimal)param.Value).ToString();
                        }
                    }
                    else
                    {
                        value = "''";
                    }
                    Regex r = new Regex(param.ParameterName);
                    sSqllog = r.Replace(sSqllog, value, 1);
                    //sSqllog = sSqllog.Replace(param.ParameterName, value);
                }
            }
            LogUtil.WriteLog(AppDomain.CurrentDomain.BaseDirectory + "\\SQLlog", DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.ToString("HH:mm:ss") + "  " + sSqllog);
        }
        #endregion
    }
}
